﻿#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2019-11-27 14:49:19
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：

import time_ext_
from ctypes import windll
import os
import threading
import sqlalchemy
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.base import Engine
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
import urllib
import cx_Oracle,pyodbc

# import cx_Oracle
import logging
import timeEXT
# Log=log.Logger(name='xx')
Base=declarative_base()

class Sql(object):
    """docstring for Sql"""
    # 初始化
    def __init__(self, database_type="mysql",*args,**kwargs):
        """摘要
        功能描述：
        参数:
            database_type: [默认参数描述: ] (默认: {"mysql"})
            *args: [描述: ]
            **kwargs: [
                        * username=用户名
                        * password=密码,
                        * host=主机IP/主机名
                        * port=端口，默认为数据库默认端口
                        * database=数据库实例
                        * dsn=odbc dsn名称
                        * databasepath= 数据库路径，sqlite,access
                    ]
        """
        super(Sql, self).__init__()
        self.__dict__=kwargs
        self.database_type = database_type
        self.port = self.get_db_port()
        self.log=logging.getLogger('main.Sql')

        self.engine=self._get_engine()
        self.connect=self.get_connect()
        self.metaData=MetaData(self.engine)
    #默认没有的属性为None
    def __getattr__(self,key):
        return None
    #获取默认端口号
    def get_db_port(self):
        t=self.database_type.lower()
        if t=='mysql':
            return 3306
        elif t in ('mssql','sqlserver','sql server'):
            return 1433
        elif t =='oracle':
            return 1521
        else:
            return self.port
    #获取连接字符串
    def _get_engine(self):
        database_type=self.database_type.lower()
        connect_str=""
        if database_type=='access' :
            connect_str=self._get_access_connect_set()
            return create_engine(connect_str,echo=True)
        elif database_type=='sqlite':
            connect_str=self._get_sqlite_connect_str()
            return create_engine(connect_str,echo=True)
        elif database_type == "mysql":
            connect_str=self._get_mysql_connect_str()
        elif database_type in ["mssql","sqlserver","sql server"] :
            connect_str=self._get_mssql_connect_str()
        elif database_type == "oracle":
            connect_str=self._get_oracle_connect_str()
        else:
            self.log.warning("目前只支持ACCESS,MYSQL,MSSQL,ORALCE,SQLITE")
            return
        if not connect_str:
            self.log.warning("创建：{}引擎失败".format(database_type))
            return
        # 创建sqlalchemy引擎
        conn_pool = create_engine(
            connect_str,
            case_sensitive=False,   # 列名不区分大小写
            echo_pool=True,         # 连接池日志显示
            max_overflow=2,         # 超过连接池大小之后，允许最大扩展连接数；
            pool_size=5,            # 连接池大小
            pool_timeout=30,        # 连接池如果没有连接了，最长等待时间
            pool_recycle=1800,        # 多久之后对连接池中连接进行一次回收
            # timeout=1,

            # echo=True
        )
        return conn_pool

    def _get_mysql_connect_str(self):
        if self.username and self.password and  self.port:
            return "mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset={5}".format(self.username,self.password,self.host or "127.0.0.1",self.port,self.database or "mysql","utf8")
    def _get_mssql_connect_str(self):
        if self.dsn and self.username and self.password :
            return "mssql+pyodbc://{}:{}@{}".format(self.username,self.password,self.dsn)
        if self.username and self.password :
            return "mssql+pymssql://{0}:{1}@{2}:{3}/{4}?charset={5}".format(self.username,self.password,self.host or "127.0.0.1",self.port,self.database or "MASTER","utf8")
    def _get_oracle_connect_str(self):
        if self.username and self.password and self.database and self.host and self.port:
            return "oracle+cx_oracle://{0}:{1}@{2}:{3}/{4}?charset={5}".format(self.username,self.password,self.host,self.port,self.database,"utf8")
    def _get_sqlite_connect_str(self):
        if self.databasepath:
            return "sqlite:///{}".format(os.path.abspath(self.databasepath))
    def _get_access_connect_set(self):
        if self.password and self.dsn:
            return "access+pyodbc://admin:{}@{}".format(self.password,self.dsn)
        elif not self.password and self.dsn:
            return "access+pyodbc://@{}".format(self.dsn)
        elif not self.dsn and self.databasepath:
            ext=os.path.splitext(self.databasepath)[1][1:].lower()
            if ext=="accdb" and 'Microsoft Access Driver (*.mdb, *.accdb)' not in [x for x in pyodbc.drivers()]:
                windll.user32.MessageBoxW(0, "不能连接到数据库:{}，请安装access2010以上版本".format(self.databasepath), "警告", 1)
            elif ext=="accdb" and "Microsoft Access Driver (*.mdb, *.accdb)" in [x for x in pyodbc.drivers()]:
                s="DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={};Pwd={};ExtendedAnsiSQL=1;".format(self.databasepath,self.password or "")
                return "access+pyodbc:///?odbc_connect={}".format(urllib.parse.quote_plus(s))
            elif ext=="mdb":
                s="DRIVER={Microsoft Access Driver (*.mdb)};DBQ={};Pwd={};ExtendedAnsiSQL=1;".format(self.databasepath,self.password or "")
                return "access+pyodbc:///?odbc_connect={}".format(urllib.parse.quote_plus(s))



    #获取连接
    def get_connect(self):
        # engine=self.get_engine()
        if self.engine:
            connect=sessionmaker(bind=self.engine)()
            # connect=self.session()
            self.log.debug("连接成功")
            return connect
        else:
            self.log.debug("连接失败")
            windll.user32.MessageBoxW(0, "无法连接到数据库，即将退出程序".format(self.databasePath), "警告", 1)
            os._exit(0)

    #获取表结构
    def get_table_structure(self,tableNameList=[]):
        tableObj=[]
        if tableNameList:
            for t in tableNameList:
                table=Table(t,self.metaData,autoload=True)
                tableObj.append(table)
        self.connect.close()

        return tableObj


if __name__ == '__main__':
    import threading,re

    a = Sql('SQL SERVER',username='ygct',password='ygct',database='ygct')
    print(a.engine.execute('select getdate()').fetchall())
    t=a.get_table_structure(['RT_DATA'])
    # print(t,dir(t[0]))
    print(dir(t[0]))
    # tt=a.engine.execute()
    i=t[0].insert().values(ID=555)
    print(i.compile().params )
    print(a.engine.execute(i))
    def run(cid):
            print(1111111,a.engine)
            conn=a.engine.connect()
        # while 1:
            rec=conn.execute(u"SELECT SYSDATE FROM DUAL").fetchall()
            print("线程：{}，查询数据：{}".format(cid,rec))
            # time.sleep(4)

    # for i in range(1):
    #     th=threading.Thread(name=i+1,target=run,args=(i+1,))
    #     th.setDaemon(True)
    #     th.start()
    #     th.join()
    # print('返回主线程')
    # while 1:
    #     pass